set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0010.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction    
go

set ansi_nulls on
set quoted_identifier on
go
/* BEGIN UPDATE SCRIPT */

--=======================================================
-- SCRIPT TO CREATE TOPIC MODULE
--=======================================================

-------- CREATE MODULE -----------

DELETE MODULES WHERE MODULE_NAME='Topics'
GO
INSERT INTO [dbo].[MODULES]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [MODULE_NAME], [DISPLAY_NAME], [RELATIVE_PATH], [MODULE_ENABLED], [TAB_ENABLED], [MOBILE_ENABLED], [TAB_ORDER], [PORTAL_ENABLED], [CUSTOM_ENABLED], [REPORT_ENABLED], [IMPORT_ENABLED], [SYNC_ENABLED], [IS_ADMIN], [CUSTOM_PAGING], [MASS_UPDATE_ENABLED], [DEFAULT_SEARCH_ENABLED], [TABLE_NAME], [EXCHANGE_SYNC], [EXCHANGE_FOLDERS], [EXCHANGE_CREATE_PARENT])
SELECT N'11e87592-56e5-4901-9fee-6747210440c7', 0, NULL, '20110818 23:00:15.130', N'00000000-0000-0000-0000-000000000001', '20111028 04:35:50.717', '20111027 21:35:50.717', N'Topics', N'.moduleList.Topics', N'~/Topics/', 1, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 1, 1, N'TOPICS', 0, 0, 0
GO
delete dbo.SHORTCUTS where MODULE_NAME='Topics'
GO
INSERT INTO [dbo].[SHORTCUTS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [MODULE_NAME], [DISPLAY_NAME], [RELATIVE_PATH], [IMAGE_NAME], [SHORTCUT_ENABLED], [SHORTCUT_ORDER], [SHORTCUT_MODULE], [SHORTCUT_ACLTYPE])
SELECT N'236bba0b-cb26-41e4-9e6c-528ad4165749', 0, NULL, getdate(), NULL, getdate(), getdate(), N'Topics', N'Topics.LNK_TOPIC_LIST', N'~/Topics/default.aspx', N'Campaigns.gif', 1, 1, N'Topics', N'edit' UNION ALL
SELECT N'38a59282-a425-4b9e-b000-8f6abe623500', 0, NULL, getdate(), NULL, getdate(), getdate(), N'Topics', N'Topics.LNK_NEW_TOPIC', N'~/Topics/edit.aspx', N'CreateCampaigns.gif', 1, 1, N'Topics', N'edit'
GO

-------------------- GRID VIEW - COLUMNS: NAME, DESCRIPTION, PARENT CATEGORY  ---------------
DELETE [dbo].[GRIDVIEWS] WHERE NAME='Topics.ListView'
INSERT INTO [dbo].[GRIDVIEWS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [NAME], [MODULE_NAME], [VIEW_NAME], [PRE_LOAD_EVENT_ID], [POST_LOAD_EVENT_ID])
SELECT N'69e91662-e5dd-4202-8d54-d835d4d1accd', 0, NULL, '20110818 23:00:13.247', N'00000000-0000-0000-0000-000000000001', '20111028 04:59:01.797', '20111027 21:59:01.797', N'Topics.ListView', N'Topics', N'vwTOPICS_List', NULL, NULL
GO

delete [dbo].[GRIDVIEWS_COLUMNS] where grid_name='Topics.ListView'
go
INSERT INTO [dbo].[GRIDVIEWS_COLUMNS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [GRID_NAME], [COLUMN_INDEX], [COLUMN_TYPE], [DEFAULT_VIEW], [HEADER_TEXT], [SORT_EXPRESSION], [ITEMSTYLE_WIDTH], [ITEMSTYLE_CSSCLASS], [ITEMSTYLE_HORIZONTAL_ALIGN], [ITEMSTYLE_VERTICAL_ALIGN], [ITEMSTYLE_WRAP], [DATA_FIELD], [DATA_FORMAT], [URL_FIELD], [URL_FORMAT], [URL_TARGET], [LIST_NAME], [URL_MODULE], [URL_ASSIGNED_FIELD], [MODULE_TYPE], [PARENT_FIELD])
SELECT N'8f477239-749d-4bac-b6f8-1d38d2f8ea4a', 0, NULL, '20111207 00:17:11.227', NULL, '20111207 00:17:11.227', '20111207 00:17:11.227', N'Topics.ListView', 3, N'TemplateColumn', 0, N'Topics.LBL_LIST_DESCRIPTION', N'DESCRIPTION', N'35%', N'listViewTdLinkS1', NULL, NULL, 1, N'Description', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT N'16ea83e3-fc19-46ff-8c6a-e67a816e71df', 0, NULL, '20111028 04:40:44.647', N'00000000-0000-0000-0000-000000000001', '20111028 04:59:01.797', '20111027 21:59:01.797', N'Topics.ListView', 2, N'TemplateColumn', 0, N'Topics.LBL_LIST_NAME', N'NAME', N'35%', N'listViewTdLinkS1', NULL, NULL, 0, N'NAME', N'HyperLink', N'ID', N'~/topics/edit.aspx?id={0}', NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
--SELECT N'7c0af8da-edf2-44cf-8119-041a338e3793', 1, NULL, '20110818 23:00:13.400', N'00000000-0000-0000-0000-000000000001', '20111028 04:59:01.797', '20111027 21:59:01.797', N'Topics.ListView', 1, N'BoundColumn', 0, N'Topics.LBL_LIST_ID', N'ID', N'35%', N'listViewTdLinkS1', NULL, NULL, NULL, N'ID', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT N'B7BB49B6-DB3F-4B98-940C-EC4116835DDE', 0, NULL, '20111028 04:40:44.647', N'00000000-0000-0000-0000-000000000001', '20111028 04:59:01.797', '20111027 21:59:01.797', N'Topics.ListView', 4, N'TemplateColumn', 0, N'Topics.LBL_CATEGORY', N'PARENT_NAME', N'35%', N'listViewTdLinkS1', NULL, NULL, 0, N'PARENT_NAME', N'HyperLink', N'PARENT_ID', N'~/topicCategories/edit.aspx?id={0}', NULL, NULL, NULL, NULL, NULL, NULL 

GO
-------------------- DATABASE-VIEW: LIST VIEW  ---------------
DROP VIEW [dbo].[vwTOPICS]
GO
CREATE VIEW [dbo].[vwTOPICS]
AS
SELECT     ID, DELETED, CREATED_BY, DATE_ENTERED, MODIFIED_USER_ID, DATE_MODIFIED, DATE_MODIFIED_UTC, NAME, PARENT_ID, DESCRIPTION, 
                      SORT_ORDER
FROM         dbo.TOPICS
where		parent_id is not null
GO
----------------------------------------------------
drop view [dbo].[vwTOPICS_List]
go
CREATE VIEW [dbo].[vwTOPICS_List]
AS
SELECT     T.ID, T. DELETED, T. CREATED_BY, T. DATE_ENTERED, T. MODIFIED_USER_ID, T. DATE_MODIFIED, T. DATE_MODIFIED_UTC, T. NAME, T. PARENT_ID, T. DESCRIPTION, T. SORT_ORDER,
		 C.NAME	PARENT_NAME
FROM        dbo.vwTOPICS T
LEFT JOIN		dbo.vwTOPIC_CATEGORIES C ON T.PARENT_ID=C.ID
go

-------------------- DATABASE-VIEW: EDIT VIEW  ---------------
DROP VIEW [dbo].[vwTOPICS_Edit]
GO
CREATE VIEW [dbo].[vwTOPICS_Edit]
AS

SELEct		t.ID, t. DELETED, t.CREATED_BY, t. DATE_ENTERED, t. MODIFIED_USER_ID, t. DATE_MODIFIED, t. DATE_MODIFIED_UTc, t. NAME, t. DESCRIPTION, t. SORT_ORDER
			, t. PARENT_ID, c.NAME PARENT_NAME
FROM		dbo.vwTOPICS t
left join		vwTOPIC_CATEGORIES c on t.PARENT_ID=c.id

GO
-------------------- EDITVIEW - FIELS: NAME AND DESCRIPTION  ---------------
DELETE EDITVIEWS_FIELDS WHERE ID='94a0e670-efa4-49a2-bf5b-55e1bf3685c9'
GO
INSERT INTO [dbo].[EDITVIEWS_FIELDS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [EDIT_NAME], [FIELD_INDEX], [FIELD_TYPE], [DEFAULT_VIEW], [DATA_LABEL], [DATA_FIELD], [DATA_FORMAT], [DISPLAY_FIELD], [CACHE_NAME], [DATA_REQUIRED], [UI_REQUIRED], [ONCLICK_SCRIPT], [FORMAT_SCRIPT], [FORMAT_TAB_INDEX], [FORMAT_MAX_LENGTH], [FORMAT_SIZE], [FORMAT_ROWS], [FORMAT_COLUMNS], [COLSPAN], [ROWSPAN], [FIELD_VALIDATOR_ID], [FIELD_VALIDATOR_MESSAGE], [MODULE_TYPE], [TOOL_TIP], [RELATED_SOURCE_MODULE_NAME], [RELATED_SOURCE_VIEW_NAME], [RELATED_SOURCE_ID_FIELD], [RELATED_SOURCE_NAME_FIELD], [RELATED_VIEW_NAME], [RELATED_ID_FIELD], [RELATED_NAME_FIELD], [RELATED_JOIN_FIELD], [PARENT_FIELD])
VALUES (N'94a0e670-efa4-49a2-bf5b-55e1bf3685c9', 0, NULL, GETDATE(), NULL, GETDATE(), GETDATE(), N'Topics.EditView', 7, N'TextBox', 0, N'Notes.LBL_DESCRIPTION', N'DESCRIPTION', NULL, NULL, NULL, 0, 0, NULL, NULL, 3, NULL, NULL, 10, 77, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO

DELETE EDITVIEWS_FIELDS WHERE ID='05a443d9-1519-4f80-b6e0-9608c3fc59d8'
GO
INSERT INTO [dbo].[EDITVIEWS_FIELDS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [EDIT_NAME], [FIELD_INDEX], [FIELD_TYPE], [DEFAULT_VIEW], [DATA_LABEL], [DATA_FIELD], [DATA_FORMAT], [DISPLAY_FIELD], [CACHE_NAME], [DATA_REQUIRED], [UI_REQUIRED], [ONCLICK_SCRIPT], [FORMAT_SCRIPT], [FORMAT_TAB_INDEX], [FORMAT_MAX_LENGTH], [FORMAT_SIZE], [FORMAT_ROWS], [FORMAT_COLUMNS], [COLSPAN], [ROWSPAN], [FIELD_VALIDATOR_ID], [FIELD_VALIDATOR_MESSAGE], [MODULE_TYPE], [TOOL_TIP], [RELATED_SOURCE_MODULE_NAME], [RELATED_SOURCE_VIEW_NAME], [RELATED_SOURCE_ID_FIELD], [RELATED_SOURCE_NAME_FIELD], [RELATED_VIEW_NAME], [RELATED_ID_FIELD], [RELATED_NAME_FIELD], [RELATED_JOIN_FIELD], [PARENT_FIELD])
VALUES( N'05a443d9-1519-4f80-b6e0-9608c3fc59d8', 0, NULL, GETDATE(), NULL, GETDATE(), GETDATE(), N'Topics.EditView', 4, N'TextBox', 0, N'Notes.LBL_SUBJECT', N'NAME', NULL, NULL, NULL, 1, 1, NULL, NULL, 1, 255, 80, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO

DELETE EDITVIEWS_FIELDS WHERE ID='4B1F2310-2E25-4425-A894-CB9EE4A0CAFD'
GO
INSERT INTO [dbo].[EDITVIEWS_FIELDS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [EDIT_NAME], [FIELD_INDEX], [FIELD_TYPE], [DEFAULT_VIEW], [DATA_LABEL], [DATA_FIELD], [DATA_FORMAT], [DISPLAY_FIELD], [CACHE_NAME], [DATA_REQUIRED], [UI_REQUIRED], [ONCLICK_SCRIPT], [FORMAT_SCRIPT], [FORMAT_TAB_INDEX], [FORMAT_MAX_LENGTH], [FORMAT_SIZE], [FORMAT_ROWS], [FORMAT_COLUMNS], [COLSPAN], [ROWSPAN], [FIELD_VALIDATOR_ID], [FIELD_VALIDATOR_MESSAGE], [MODULE_TYPE], [TOOL_TIP], [RELATED_SOURCE_MODULE_NAME], [RELATED_SOURCE_VIEW_NAME], [RELATED_SOURCE_ID_FIELD], [RELATED_SOURCE_NAME_FIELD], [RELATED_VIEW_NAME], [RELATED_ID_FIELD], [RELATED_NAME_FIELD], [RELATED_JOIN_FIELD], [PARENT_FIELD])
VALUES( 
  N'4B1F2310-2E25-4425-A894-CB9EE4A0CAFD', 0, NULL, '20110818 23:00:09.973', NULL, '20110818 23:00:09.973', '20110818 16:00:09.973', N'Topics.EditView', 6, N'Listbox', 0, N'Topics.LBL_CATEGORY', N'PARENT_ID', null, NULL, 'CallTopicCategories', 1, 1, NULL, NULL, 1, 255, 80, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

go

-------------------------------------------------
-- Database: SP Update
-------------------------------------------------
drop PROC [dbo].[spTOPICS_Update]
go
create PROC [dbo].[spTOPICS_Update]
	@ID UNIQUEIDENTIFIER OUTPUT,
	@NAME NVARCHAR(255),
	@DESCRIPTION NVARCHAR(MAX),
	@PARENT_ID UNIQUEIDENTIFIER = null,
	@MODIFIED_USER_ID UNIQUEIDENTIFIER
AS
	IF @Id IS NULL
	BEGIN
		SET @ID = NEWID()
		INSERT INTO dbo.TOPICS(ID, [NAME], DESCRIPTION, DATE_ENTERED, CREATED_BY,PARENT_ID)
		VALUES(@ID, @NAME, @DESCRIPTION, GETDATE(), @MODIFIED_USER_ID,@PARENT_ID)
	END
	ELSE
	BEGIN
		SET ROWCOUNT 1
		UPDATE dbo.TOPICS SET
			[NAME] = @NAME,
			DESCRIPTION = @DESCRIPTION,
			DATE_MODIFIED = GETDATE(),
			MODIFIED_USER_ID = @MODIFIED_USER_ID,
			PARENT_ID = @PARENT_ID
			
		WHERE
			ID = @ID
	END

go
------------------------------------
-- TERMINOLOGY
------------------------------------

DELETE TERMINOLOGY WHERE ID='5A95844A-12CB-4BF1-B56B-CD385367BE5C'
GO
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],[MODULE_NAME],[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   ('5A95844A-12CB-4BF1-B56B-CD385367BE5C'
	   ,'LBL_CATEGORY'
	   ,'Topics'
	   ,'Category'
	   ,'en-US',0,getdate(),getdate(),getdate())

/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
